﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    public class UsersDBAccess
    {

        /// <summary>
        /// Insert Users
        /// </summary>
        /// <param name="objUsers"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool AddNewUsers(Users objUsers, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {                
                new SqlParameter("@UserID", objUsers.UserID),
                new SqlParameter("@Created", objUsers.Created),
                new SqlParameter("@Updated",objUsers.Updated),
                new SqlParameter("@Isactive", objUsers.Isactive),
                new SqlParameter("@UserName", objUsers.UserName),
                new SqlParameter("@Password", objUsers.Password),
                new SqlParameter("@Email", objUsers.Email),
                new SqlParameter("@Phone", objUsers.Phone),
                new SqlParameter("@block", objUsers.Block),
                new SqlParameter("@Level", objUsers.Level),
                new SqlParameter("@Descriptions", objUsers.Descriptions),
                new SqlParameter("@FullName", objUsers.FullName),
                new SqlParameter("@Address", objUsers.Address),
                new SqlParameter("@IdentityCard", objUsers.IdentityCard),
            };

            return SqlDBHelper.ExecuteNonQuery("AddNewUsers", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Update Users
        /// </summary>
        /// <param name="objUsers"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool UpdateUsers(Users objUsers, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {

                new SqlParameter("@Created", objUsers.Created),
                new SqlParameter("@Updated",objUsers.Updated),
                new SqlParameter("@Isactive", objUsers.Isactive),
                new SqlParameter("@UserName", objUsers.UserName),
                new SqlParameter("@Password", objUsers.Password),
                new SqlParameter("@Email", objUsers.Email),
                new SqlParameter("@Phone", objUsers.Phone),
                new SqlParameter("@block", objUsers.Block),
                new SqlParameter("@Level", objUsers.Level),
                new SqlParameter("@Descriptions", objUsers.Descriptions),
                new SqlParameter("@FullName", objUsers.FullName),
                new SqlParameter("@Address", objUsers.Address),
                new SqlParameter("@IdentityCard", objUsers.IdentityCard),

               
                
            };

            return SqlDBHelper.ExecuteNonQuery("UpdateUsers", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Delete Users
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool DeleteUsers(int UserID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserID", UserID)
            };

            return SqlDBHelper.ExecuteNonQuery("DeleteUsers", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Get Users Details
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public Users GetUsersDetails(int UserID, String CONNECTION_STRING)
        {
            Users objUsers = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserID",UserID),
            };
            //Lets get the list of all Users in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetUsersDetails", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Users
                    objUsers = new Users();

                    //Now lets populate Users details into the list of Users                                       
                    objUsers.UserID = Convert.ToInt32(row["UserID"]);
                    objUsers.Created = row["Created"].ToString();
                    objUsers.Updated = row["Updated"].ToString();
                    objUsers.Isactive = row["Isactive"].ToString();
                    objUsers.UserName = row["UserName"].ToString();
                    objUsers.Password = row["Passwords"].ToString();
                    objUsers.Email = row["Email"].ToString();
                    objUsers.Phone = row["Phone"].ToString();
                    objUsers.Level = row["Level"].ToString();
                    objUsers.Descriptions = row["Descriptions"].ToString();
                    objUsers.Block = row["Block"].ToString();
                    objUsers.FullName = row["FullName"].ToString();
                    objUsers.Address = row["Address"].ToString();
                    objUsers.IdentityCard = row["IdentityCard"].ToString();
                }
            }

            return objUsers;
        }


        /// <summary>
        /// Get Users Details for Private
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public Users GetUsersDetailsForPrivate(String UserID, String CONNECTION_STRING)
        {
            Users objUsers = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserID",UserID),
            };
            //Lets get the list of all Users in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetUsersDetailsForPrivate", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Users
                    objUsers = new Users();

                    //Now lets populate Users details into the list of Users                                       
                    objUsers.UserID = Convert.ToInt32(row["UserID"]);
                    objUsers.Created = row["Created"].ToString();
                    objUsers.Updated = row["Updated"].ToString();
                    objUsers.Isactive = row["Isactive"].ToString();
                    objUsers.UserName = row["UserName"].ToString();
                    objUsers.Password = row["Passwords"].ToString();
                    objUsers.Email = row["Email"].ToString();
                    objUsers.Phone = row["Phone"].ToString();
                    objUsers.Level = row["Level"].ToString();
                    objUsers.Descriptions = row["Descriptions"].ToString();
                    objUsers.Block = row["Block"].ToString();
                    objUsers.FullName = row["FullName"].ToString();
                    objUsers.Address = row["Address"].ToString();
                    objUsers.IdentityCard = row["IdentityCard"].ToString();
                }
            }

            return objUsers;
        }


        /// <summary>
        /// Get Users Details
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public Users GetUsersDetailsWithUserName(String UserName, String CONNECTION_STRING)
        {
            Users objUsers = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@UserName",UserName),
            };
            //Lets get the list of all Users in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetUsersDetailsWithUserName", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Users
                    objUsers = new Users();

                    //Now lets populate Users details into the list of Users                                       
                    objUsers.UserID = Convert.ToInt32(row["UserID"]);
                    objUsers.Created = row["Created"].ToString();
                    objUsers.Updated = row["Updated"].ToString();
                    objUsers.Isactive = row["Isactive"].ToString();
                    objUsers.UserName = row["UserName"].ToString();
                    objUsers.Password = row["Passwords"].ToString();
                    objUsers.Email = row["Email"].ToString();
                    objUsers.Phone = row["Phone"].ToString();
                    objUsers.Level = row["Level"].ToString();
                    objUsers.Descriptions = row["Descriptions"].ToString();
                    objUsers.Block = row["Block"].ToString();
                    objUsers.FullName = row["FullName"].ToString();
                    objUsers.Address = row["Address"].ToString();
                    objUsers.IdentityCard = row["IdentityCard"].ToString();
                }
            }

            return objUsers;
        }


        /// <summary>
        /// Get all Users 
        /// </summary>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public List<Users> GetAllUsers(String CONNECTION_STRING)
        {
            List<Users> listUsers = null;

            //Lets get the list of all Users in a datataable
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetAllUsers", CommandType.StoredProcedure, CONNECTION_STRING))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of Users
                    listUsers = new List<Users>();

                    //Now lets populate Users details into the list of Users
                    foreach (DataRow row in table.Rows)
                    {
                        Users objUsers = new Users();
                        objUsers.UserID = Convert.ToInt32(row["UserID"]);
                        objUsers.Created = row["Created"].ToString();
                        objUsers.Updated = row["Updated"].ToString();
                        objUsers.Isactive = row["Isactive"].ToString();
                        objUsers.UserName = row["UserName"].ToString();
                        objUsers.Password = row["Passwords"].ToString();
                        objUsers.Email = row["Email"].ToString();
                        objUsers.Phone = row["Phone"].ToString();
                        objUsers.Level = row["Level"].ToString();
                        objUsers.Descriptions = row["Descriptions"].ToString();
                        objUsers.Block = row["Block"].ToString();
                        objUsers.FullName = row["FullName"].ToString();
                        objUsers.Address = row["Address"].ToString();
                        objUsers.IdentityCard = row["IdentityCard"].ToString();
                        listUsers.Add(objUsers);
                    }
                }
            }

            return listUsers;
        }        

        /// <summary>
        /// Get Users List
        /// </summary>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public List<Users> GetUsersList(String CONNECTION_STRING)
        {
            List<Users> listUsers = null;

            //Lets get the list of all Users in a datataable
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetUsersList", CommandType.StoredProcedure, CONNECTION_STRING))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of Users
                    listUsers = new List<Users>();

                    //Now lets populate Users details into the list of Users
                    foreach (DataRow row in table.Rows)
                    {
                        Users objUsers = new Users();
                        objUsers.UserID = Convert.ToInt32(row["UserID"]);
                        objUsers.Created = row["Created"].ToString();
                        objUsers.Updated = row["Updated"].ToString();
                        objUsers.Isactive = row["Isactive"].ToString();
                        objUsers.UserName = row["UserName"].ToString();
                        objUsers.Password = row["Passwords"].ToString();
                        objUsers.Email = row["Email"].ToString();
                        objUsers.Phone = row["Phone"].ToString();
                        objUsers.Level = row["Level"].ToString();
                        objUsers.Descriptions = row["Descriptions"].ToString();
                        objUsers.Block = row["Block"].ToString();
                        objUsers.FullName = row["FullName"].ToString();
                        objUsers.Address = row["Address"].ToString();
                        objUsers.IdentityCard = row["IdentityCard"].ToString();
                        listUsers.Add(objUsers);
                    }
                }
            }

            return listUsers;
        }        
    }
}
